{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "\n",
    "#Python SQLITE数据库是一款非常小巧的嵌入式开源数据库软件\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#数据集太大，读10000条记录看看\n",
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'E:/csdn/week5/train_triplets.txt', \n",
    "                              nrows=5000,sep='\\t', header=None, \n",
    "                              names=['user','song','play_count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAKIMP12A8C130995</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAPDEY12A81C210A9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBBMDR12A8C13253B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFNSP12AF72A0E22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFOVM12A58A7D494</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBNZDC12A6D4FC103</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBSUJE12A6D4F8CF5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBVFZR12A6D4F8AE3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXALG12A8C13C108</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXHDL12A81C204C0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  play_count\n",
       "0  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAKIMP12A8C130995           1\n",
       "1  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAPDEY12A81C210A9           1\n",
       "2  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBBMDR12A8C13253B           2\n",
       "3  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFNSP12AF72A0E22           1\n",
       "4  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFOVM12A58A7D494           1\n",
       "5  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBNZDC12A6D4FC103           1\n",
       "6  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBSUJE12A6D4F8CF5           2\n",
       "7  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBVFZR12A6D4F8AE3           1\n",
       "8  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXALG12A8C13C108           1\n",
       "9  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXHDL12A81C204C0           1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_dict = {} #user及对应的play_count次数集合(在所有的歌曲上求和)\n",
    "with open('E:/csdn/week5/train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        user = line.split('\\t')[0]            #第一列为用户id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if user in output_dict:\n",
    "            play_count +=output_dict[user]\n",
    "            output_dict.update({user:play_count})\n",
    "        output_dict.update({user:play_count})\n",
    "output_list = [{'user':k,'play_count':v} for k,v in output_dict.items()]\n",
    "play_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "play_count_df = play_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "play_count_df.to_csv(path_or_buf='E:/csdn/week5/user_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_dict = {}\n",
    "with open('E:/csdn/week5/train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        song = line.split('\\t')[1]            #第二列为歌曲id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if song in output_dict:\n",
    "            play_count +=output_dict[song]\n",
    "            output_dict.update({song:play_count})\n",
    "        output_dict.update({song:play_count})\n",
    "output_list = [{'song':k,'play_count':v} for k,v in output_dict.items()]\n",
    "song_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "song_count_df = song_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_count_df.to_csv(path_or_buf='E:/csdn/week5/song_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>user</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13132</td>\n",
       "      <td>093cb74eb3c517c5179ae24caf0ebec51b24d2a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9884</td>\n",
       "      <td>119b7c88d58d0c6eb051365c103da5caf817bea6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8210</td>\n",
       "      <td>3fa44653315697f42410a30cb766a4eb102080bb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7015</td>\n",
       "      <td>a2679496cd0af9779a92a13ff7c6af5c81ea8c7b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6494</td>\n",
       "      <td>d7d2d888ae04d16e994d6964214a1de81392ee04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6472</td>\n",
       "      <td>4ae01afa8f2430ea0704d502bc7b57fb52164882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6150</td>\n",
       "      <td>b7c24f770be6b802805ac0e2106624a517643c17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5656</td>\n",
       "      <td>113255a012b2affeab62607563d03fbdf31b08e7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5620</td>\n",
       "      <td>6d625c6557df84b60d90426c0116138b617b9449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5602</td>\n",
       "      <td>99ac3d883681e21ea68071019dba828ce76fe94d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                                      user\n",
       "0       13132  093cb74eb3c517c5179ae24caf0ebec51b24d2a2\n",
       "1        9884  119b7c88d58d0c6eb051365c103da5caf817bea6\n",
       "2        8210  3fa44653315697f42410a30cb766a4eb102080bb\n",
       "3        7015  a2679496cd0af9779a92a13ff7c6af5c81ea8c7b\n",
       "4        6494  d7d2d888ae04d16e994d6964214a1de81392ee04\n",
       "5        6472  4ae01afa8f2430ea0704d502bc7b57fb52164882\n",
       "6        6150  b7c24f770be6b802805ac0e2106624a517643c17\n",
       "7        5656  113255a012b2affeab62607563d03fbdf31b08e7\n",
       "8        5620  6d625c6557df84b60d90426c0116138b617b9449\n",
       "9        5602  99ac3d883681e21ea68071019dba828ce76fe94d"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "play_count_df = pd.read_csv(filepath_or_buffer='E:/csdn/week5/user_playcount_df.csv')\n",
    "play_count_df.head(n =10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>song</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>726885</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648239</td>\n",
       "      <td>SOAUWYT12A81C206F1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>527893</td>\n",
       "      <td>SOSXLTC12AF72A7F54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>425463</td>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>389880</td>\n",
       "      <td>SOEGIYH12A6D4FC0E3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>356533</td>\n",
       "      <td>SOAXGDH12A8C13F8A1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>292642</td>\n",
       "      <td>SONYKOW12AB01849C9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>274627</td>\n",
       "      <td>SOPUCYA12A8C13A694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>268353</td>\n",
       "      <td>SOUFTBI12AB0183F65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>244730</td>\n",
       "      <td>SOVDSJC12A58A7A271</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                song\n",
       "0      726885  SOBONKR12A58A7A7E0\n",
       "1      648239  SOAUWYT12A81C206F1\n",
       "2      527893  SOSXLTC12AF72A7F54\n",
       "3      425463  SOFRQTD12A81C233C0\n",
       "4      389880  SOEGIYH12A6D4FC0E3\n",
       "5      356533  SOAXGDH12A8C13F8A1\n",
       "6      292642  SONYKOW12AB01849C9\n",
       "7      274627  SOPUCYA12A8C13A694\n",
       "8      268353  SOUFTBI12AB0183F65\n",
       "9      244730  SOVDSJC12A58A7A271"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_count_df = pd.read_csv(filepath_or_buffer='E:/csdn/week5/song_playcount_df.csv')\n",
    "song_count_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_play_count = sum(song_count_df.play_count)\n",
    "(float(play_count_df.head(n=5000).play_count.sum())/total_play_count)*100\n",
    "play_count_subset = play_count_df.head(n=5000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "47.16585043768635"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#取3万首歌曲（10%）\n",
    "#前3万首歌曲的播放次数占总播放次数的80%\n",
    "(float(song_count_df.head(n=5000).play_count.sum())/total_play_count)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(493950, 3)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_count_subset = song_count_df.head(n=5000)\n",
    "user_subset = list(play_count_subset.user)\n",
    "song_subset = list(song_count_subset.song)\n",
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'E:/csdn/week5/train_triplets.txt',sep='\\t', \n",
    "                              header=None, names=['user','song','play_count'])\n",
    "triplet_dataset_sub = triplet_dataset[triplet_dataset.user.isin(user_subset) ]\n",
    "del(triplet_dataset)\n",
    "triplet_dataset_sub_song = triplet_dataset_sub[triplet_dataset_sub.song.isin(song_subset)]\n",
    "del(triplet_dataset_sub)\n",
    "triplet_dataset_sub_song.to_csv(path_or_buf ='E:/csdn/week5/triplet_dataset_sub_song.csv', index=False)\n",
    "triplet_dataset_sub_song.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAIILB12A58A776F7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAJJDS12A8C13A3FB</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAMDXO12A8C131E2F</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAMPRJ12A8AE45F38</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAPIHX12AB0184CB1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAQTNT12A6701F957</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>573</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOATNYF12AF72A8D48</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>574</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAUBGU12A6701C57A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAUXEN12A81C23960</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         user                song  play_count\n",
       "560  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAFTRR12AF72A8D4D           1\n",
       "561  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAIILB12A58A776F7           3\n",
       "563  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAJJDS12A8C13A3FB           1\n",
       "566  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAMDXO12A8C131E2F           2\n",
       "567  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAMPRJ12A8AE45F38          20\n",
       "570  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAPIHX12AB0184CB1           7\n",
       "571  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAQTNT12A6701F957           2\n",
       "573  5a905f000fc1ff3df7ca807d57edb608863db05d  SOATNYF12AF72A8D48           1\n",
       "574  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAUBGU12A6701C57A           2\n",
       "576  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAUXEN12A81C23960          11"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('songs',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect('E:/csdn/week5/track_metadata.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT name FROM sqlite_master WHERE type='table'\")\n",
    "cur.fetchall()  #数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "track_metadata_df = pd.read_sql(con=conn, sql='select * from songs')\n",
    "\n",
    "#只保留流行歌曲（3万首）的元数据\n",
    "track_metadata_df_sub = track_metadata_df[track_metadata_df.song_id.isin(song_subset)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_id</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_mbid</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>412</th>\n",
       "      <td>TRMMFSL128F4234583</td>\n",
       "      <td>Drunk and Hot Girls</td>\n",
       "      <td>SOGKGLB12A81C22AFA</td>\n",
       "      <td>Graduation</td>\n",
       "      <td>ARRH63Y1187FB47783</td>\n",
       "      <td>164f0d73-1234-4e2c-8743-d77bf2191051</td>\n",
       "      <td>Kanye West / Mos Def</td>\n",
       "      <td>313.28608</td>\n",
       "      <td>0.877214</td>\n",
       "      <td>1.082503</td>\n",
       "      <td>2007</td>\n",
       "      <td>1356708</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>634</th>\n",
       "      <td>TRMMABF128F425862B</td>\n",
       "      <td>Heroes Get Remembered_ Legends Never Die</td>\n",
       "      <td>SOBQZKH12A8AE48A02</td>\n",
       "      <td>Rise Or Die Trying</td>\n",
       "      <td>ARF4I701187FB3AEFF</td>\n",
       "      <td>c0a138e2-cef7-471b-8fbb-524e1076256a</td>\n",
       "      <td>Four Year Strong</td>\n",
       "      <td>215.87546</td>\n",
       "      <td>0.672685</td>\n",
       "      <td>0.573963</td>\n",
       "      <td>2007</td>\n",
       "      <td>3099052</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>932</th>\n",
       "      <td>TRMMLZZ128F93049D5</td>\n",
       "      <td>Nostalgia Amnesia</td>\n",
       "      <td>SOTUYMM12AAF3B336B</td>\n",
       "      <td>Melancholydisco</td>\n",
       "      <td>ARD7IFY1187B99E0CE</td>\n",
       "      <td>f74784e9-600c-4bdf-a324-b1dd430362a4</td>\n",
       "      <td>Viola</td>\n",
       "      <td>260.25751</td>\n",
       "      <td>0.523926</td>\n",
       "      <td>0.351803</td>\n",
       "      <td>0</td>\n",
       "      <td>6863489</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1226</th>\n",
       "      <td>TRMMDFO128F1473E53</td>\n",
       "      <td>Voy A Pasarmelo Bien</td>\n",
       "      <td>SOGHDIA12A6D4F7C96</td>\n",
       "      <td>1984 - 1992 Disco Libro</td>\n",
       "      <td>AR69GGX1187B98E1F7</td>\n",
       "      <td>8b1628a7-130a-4faf-96d8-2418bd9043c2</td>\n",
       "      <td>Hombres G</td>\n",
       "      <td>236.56444</td>\n",
       "      <td>0.632210</td>\n",
       "      <td>0.498633</td>\n",
       "      <td>2006</td>\n",
       "      <td>371248</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1242</th>\n",
       "      <td>TRMMDVB12903CE2FF6</td>\n",
       "      <td>Redneck Yacht Club</td>\n",
       "      <td>SOVHIAK12A8C144F9C</td>\n",
       "      <td>My Kind Of Livin'</td>\n",
       "      <td>ARKJMAV1187B98C736</td>\n",
       "      <td>1415b94a-ae0d-4918-9100-1943e8227f1b</td>\n",
       "      <td>Craig Morgan</td>\n",
       "      <td>227.57832</td>\n",
       "      <td>0.731885</td>\n",
       "      <td>0.501185</td>\n",
       "      <td>2005</td>\n",
       "      <td>8076599</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                track_id                                     title  \\\n",
       "412   TRMMFSL128F4234583                       Drunk and Hot Girls   \n",
       "634   TRMMABF128F425862B  Heroes Get Remembered_ Legends Never Die   \n",
       "932   TRMMLZZ128F93049D5                         Nostalgia Amnesia   \n",
       "1226  TRMMDFO128F1473E53                      Voy A Pasarmelo Bien   \n",
       "1242  TRMMDVB12903CE2FF6                        Redneck Yacht Club   \n",
       "\n",
       "                 song_id                  release           artist_id  \\\n",
       "412   SOGKGLB12A81C22AFA               Graduation  ARRH63Y1187FB47783   \n",
       "634   SOBQZKH12A8AE48A02       Rise Or Die Trying  ARF4I701187FB3AEFF   \n",
       "932   SOTUYMM12AAF3B336B          Melancholydisco  ARD7IFY1187B99E0CE   \n",
       "1226  SOGHDIA12A6D4F7C96  1984 - 1992 Disco Libro  AR69GGX1187B98E1F7   \n",
       "1242  SOVHIAK12A8C144F9C        My Kind Of Livin'  ARKJMAV1187B98C736   \n",
       "\n",
       "                               artist_mbid           artist_name   duration  \\\n",
       "412   164f0d73-1234-4e2c-8743-d77bf2191051  Kanye West / Mos Def  313.28608   \n",
       "634   c0a138e2-cef7-471b-8fbb-524e1076256a      Four Year Strong  215.87546   \n",
       "932   f74784e9-600c-4bdf-a324-b1dd430362a4                 Viola  260.25751   \n",
       "1226  8b1628a7-130a-4faf-96d8-2418bd9043c2             Hombres G  236.56444   \n",
       "1242  1415b94a-ae0d-4918-9100-1943e8227f1b          Craig Morgan  227.57832   \n",
       "\n",
       "      artist_familiarity  artist_hotttnesss  year  track_7digitalid  shs_perf  \\\n",
       "412             0.877214           1.082503  2007           1356708        -1   \n",
       "634             0.672685           0.573963  2007           3099052        -1   \n",
       "932             0.523926           0.351803     0           6863489        -1   \n",
       "1226            0.632210           0.498633  2006            371248        -1   \n",
       "1242            0.731885           0.501185  2005           8076599        -1   \n",
       "\n",
       "      shs_work  \n",
       "412          0  \n",
       "634          0  \n",
       "932          0  \n",
       "1226         0  \n",
       "1242         0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'sys' (built-in)>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "import sys\n",
    "import imp\n",
    "imp.reload(sys)\n",
    "#sys.setdefaultencoding(\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "track_metadata_df_sub.to_csv(path_or_buf ='E:/csdn/week5/track_metadata_df_sub.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5179, 14)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song = pd.read_csv(filepath_or_buffer = 'E:/csdn/week5/triplet_dataset_sub_song.csv')\n",
    "track_metadata_df_sub = pd.read_csv(filepath_or_buffer = 'E:/csdn/week5/track_metadata_df_sub.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "del(track_metadata_df_sub['track_id'])\n",
    "del(track_metadata_df_sub['artist_mbid'])\n",
    "\n",
    "#去除重复记录\n",
    "track_metadata_df_sub = track_metadata_df_sub.drop_duplicates(['song_id'])\n",
    "\n",
    "#播放历史和歌曲元数据合并\n",
    "triplet_dataset_sub_song_merged = pd.merge(triplet_dataset_sub_song, track_metadata_df_sub, how='left', left_on='song', right_on='song_id')\n",
    "triplet_dataset_sub_song_merged.rename(columns={'play_count':'listen_count'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#去掉一些不用的列\n",
    "del(triplet_dataset_sub_song_merged['song_id'])\n",
    "del(triplet_dataset_sub_song_merged['artist_id'])\n",
    "del(triplet_dataset_sub_song_merged['duration'])\n",
    "del(triplet_dataset_sub_song_merged['artist_familiarity'])\n",
    "del(triplet_dataset_sub_song_merged['artist_hotttnesss'])\n",
    "del(triplet_dataset_sub_song_merged['track_7digitalid'])\n",
    "del(triplet_dataset_sub_song_merged['shs_perf'])\n",
    "del(triplet_dataset_sub_song_merged['shs_work'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "      <td>Harder Better Faster Stronger</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAIILB12A58A776F7</td>\n",
       "      <td>3</td>\n",
       "      <td>Phantom Part 1.5 (Album Version)</td>\n",
       "      <td>A Cross The Universe</td>\n",
       "      <td>Justice</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAJJDS12A8C13A3FB</td>\n",
       "      <td>1</td>\n",
       "      <td>I Got Mine</td>\n",
       "      <td>Attack &amp; Release</td>\n",
       "      <td>The Black Keys</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAMDXO12A8C131E2F</td>\n",
       "      <td>2</td>\n",
       "      <td>Pogo</td>\n",
       "      <td>Idealism</td>\n",
       "      <td>Digitalism</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAMPRJ12A8AE45F38</td>\n",
       "      <td>20</td>\n",
       "      <td>Rorol</td>\n",
       "      <td>Identification Parade</td>\n",
       "      <td>Octopus Project</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAPIHX12AB0184CB1</td>\n",
       "      <td>7</td>\n",
       "      <td>Auto-Dub</td>\n",
       "      <td>Skream!</td>\n",
       "      <td>Skream</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAQTNT12A6701F957</td>\n",
       "      <td>2</td>\n",
       "      <td>That Was Just A Dream</td>\n",
       "      <td>Bright Like Neon Love</td>\n",
       "      <td>Cut Copy</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOATNYF12AF72A8D48</td>\n",
       "      <td>1</td>\n",
       "      <td>Aerodynamic</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAUBGU12A6701C57A</td>\n",
       "      <td>2</td>\n",
       "      <td>Swallowed In The Sea</td>\n",
       "      <td>X &amp; Y</td>\n",
       "      <td>Coldplay</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5a905f000fc1ff3df7ca807d57edb608863db05d</td>\n",
       "      <td>SOAUXEN12A81C23960</td>\n",
       "      <td>11</td>\n",
       "      <td>Hilarious Movie Of The 90s</td>\n",
       "      <td>Pause</td>\n",
       "      <td>Four Tet</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  listen_count  \\\n",
       "0  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAFTRR12AF72A8D4D             1   \n",
       "1  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAIILB12A58A776F7             3   \n",
       "2  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAJJDS12A8C13A3FB             1   \n",
       "3  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAMDXO12A8C131E2F             2   \n",
       "4  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAMPRJ12A8AE45F38            20   \n",
       "5  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAPIHX12AB0184CB1             7   \n",
       "6  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAQTNT12A6701F957             2   \n",
       "7  5a905f000fc1ff3df7ca807d57edb608863db05d  SOATNYF12AF72A8D48             1   \n",
       "8  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAUBGU12A6701C57A             2   \n",
       "9  5a905f000fc1ff3df7ca807d57edb608863db05d  SOAUXEN12A81C23960            11   \n",
       "\n",
       "                              title                release      artist_name  \\\n",
       "0     Harder Better Faster Stronger              Discovery        Daft Punk   \n",
       "1  Phantom Part 1.5 (Album Version)   A Cross The Universe          Justice   \n",
       "2                        I Got Mine       Attack & Release   The Black Keys   \n",
       "3                              Pogo               Idealism       Digitalism   \n",
       "4                             Rorol  Identification Parade  Octopus Project   \n",
       "5                          Auto-Dub                Skream!           Skream   \n",
       "6             That Was Just A Dream  Bright Like Neon Love         Cut Copy   \n",
       "7                       Aerodynamic              Discovery        Daft Punk   \n",
       "8              Swallowed In The Sea                  X & Y         Coldplay   \n",
       "9        Hilarious Movie Of The 90s                  Pause         Four Tet   \n",
       "\n",
       "   year  \n",
       "0  2007  \n",
       "1     0  \n",
       "2  2008  \n",
       "3  2007  \n",
       "4  2002  \n",
       "5  2006  \n",
       "6  2004  \n",
       "7  2001  \n",
       "8  2005  \n",
       "9  2001  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song_merged.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 推荐系统使用的数据集\n",
    "triplet_dataset_sub_song_merged.to_csv(path_or_buf ='E:/csdn/week5/triplet_dataset_sub_song_merged.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
